package cn.wj.school.cloud.utils;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import lombok.Data;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @Author: yueding
 * @Description:
 * @Date: Created in 19:47 2019/1/3
 * @Modified By: yueding
 */
public class DB2Domain {

    private static List<String> tableNames = Arrays.asList("sy_login_log");
    private static Map<String, List<DBMap>> tableMap = Maps.newHashMap();
    private static Map<String, String> tableCommentMap = Maps.newHashMap();
    private static Map<String, String> typeMap = Maps.newHashMap();
    private static final String baseDomainPath = "/Users/lidapeng/Documents/workspace/jteach/jteach-cloud/jteach-cloud-center/src/main/java/";
    private static final String baseDtoPath = "/Users/lidapeng/Documents/workspace/jteach/jteach-cloud/jteach-cloud-api/src/main/java/";
    private static final String packageDomainPath = "cn/wj/school/cloud/model";
    private static final String packageDtoPath = "cn/wj/school/cloud/dto";

    public static void main(String[] args) {
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        final String time = sf.format(new Date());
        // String testFilePath = baseDomainPath +"../../test/resources/";
        // final String domainPath = testFilePath+"domain/";
        // final String dtoPath = testFilePath+"dto/";
        final String domainPath = baseDomainPath + packageDomainPath + "/";
        final String dtoPath = baseDtoPath + packageDtoPath + "/";
        DB2Domain db2Domain = new DB2Domain();
        Connection conn = db2Domain.getConnection();
        tableNames.forEach(t -> {
            db2Domain.excuteSql(conn, t);
        });
        tableNames.forEach(t -> {
            List<DBMap> dbMaps = tableMap.get(t);
            String className = db2Domain._2field(t.replaceFirst(t.substring(0, 1), t.substring(0, 1).toUpperCase()));
           // className = className.substring(2, className.length());
            StringBuilder sbDomain = new StringBuilder();
            StringBuilder sbDto = new StringBuilder();
            sbDomain.append("package " + packageDomainPath.replace("/", ".") + ";\r\n\r\n");
            sbDomain.append("import lombok.Data;\r\n\r\n");
            sbDomain.append("import java.io.Serializable;\r\n");
            sbDomain.append("import java.util.Date;\r\n\r\n");
            if (!StringUtils.isBlank(tableCommentMap.get(t))) {
                sbDomain.append("/**" + "\r\n");
                sbDomain.append(" *" + " Created by yueding on " + time + ".\r\n");
                sbDomain.append(" *" + " Description:" + tableCommentMap.get(t) + "\r\n");
                sbDomain.append(" */" + "\r\n");
            }
            sbDomain.append("@Data\r\n");
            sbDomain.append("public class " + className + " implements Serializable {\r\n");
            sbDomain.append("    private static final long serialVersionUID = 1L;\r\n\r\n");
            sbDto.append("package " + packageDtoPath.replace("/", ".") + ";\r\n\r\n");
            sbDto.append("import lombok.Data;\r\n\r\n");
            sbDto.append("import java.io.Serializable;\r\n");
            sbDto.append("import java.util.Date;\r\n\r\n");
            if (!StringUtils.isBlank(tableCommentMap.get(t))) {
                sbDto.append("/**" + "\r\n");
                sbDto.append(" *" + " Created by yueding on " + time + ".\r\n");
                sbDto.append(" *" + " Description:" + tableCommentMap.get(t) + "\r\n");
                sbDto.append(" *" + "\r\n");
                sbDto.append(" *" + " @author <a href=\"mailto:lidapeng@cai-inc.com\"/>" + "\r\n");
                sbDto.append(" */" + "\r\n");
            }
            sbDto.append("@Data\r\n");
            sbDto.append("public class " + className + "DTO" + " implements Serializable {\r\n");
            sbDto.append("    private static final long serialVersionUID = 1L;\r\n\r\n");
            dbMaps.forEach(dbMap -> {
                String type = "";
                if (dbMap.getField().equals("file_oss_id")) {
                    type = typeMap.get("varchar");
                } else if (dbMap.getField().indexOf("id") > 0 || dbMap.getField().equals("id")) {
                    type = typeMap.get("long");
                } else {
                    type = typeMap.get(dbMap.getType());
                }
                sbDomain.append("    /**" + "\r\n");
                sbDomain.append("     * " + dbMap.getMemo() + "\r\n");
                sbDomain.append("     */" + "\r\n");
                sbDomain.append("    " + "private " + type + " " + db2Domain._2field(dbMap.getField()) + ";\r\n");

                sbDto.append("    /**" + "\r\n");
                sbDto.append("     * " + dbMap.getMemo() + "\r\n");
                sbDto.append("     */" + "\r\n");
                sbDto.append("    " + "private " + type + " " + db2Domain._2field(dbMap.getField()) + ";\r\n");
            });
            sbDomain.append("}\r\n\r\n");
            sbDto.append("}\r\n\r\n");
            CommonUtils.genFile(domainPath + className + ".java", sbDomain.toString());
            CommonUtils.genFile(dtoPath + className + "DTO.java", sbDto.toString());
        });

    }

    public DB2Domain() {
        typeMap.put("datetime", "Date");
        typeMap.put("date", "Date");
        typeMap.put("varchar", "String");
        typeMap.put("char", "String");
        typeMap.put("tinyint", "Integer");
        typeMap.put("int", "Integer");
        typeMap.put("long", "Long");
        typeMap.put("bigint", "Long");
        typeMap.put("smallint", "Integer");
        typeMap.put("decimal", "Double");
    }


    private String _2field(String input) {
        Pattern p = Pattern.compile("_[a-zA-Z]");
        Matcher match = p.matcher(input);
        while (match.find()) {
            String str = match.group();
            input = input.replaceFirst(str, str.substring(str.indexOf("_") + 1).toUpperCase());
        }
        return input;
    }

    private final String SELECT_SQL_FIELD = " column_name as field,";
    private final String SELECT_SQL_TYPE = " data_type as type,";
    private final String SELECT_SQL_MEMO = " column_comment as memo,";
    private final String SELECT_SQL_MUNERIC_LENGTH = " numeric_precision as munericLength,";
    private final String SELECT_SQL_NUMERIC_SCALE = " numeric_scale as numericScale, ";
    private final String SELECT_SQL_ISNULLABLE = " is_nullable as isNullable,";
    private final String SELECT_SQL_EXTRA = " CASE WHEN extra = 'auto_increment' THEN 1 ELSE 0 END as extra,";
    private final String SELECT_SQL_ISDEFAULT = " column_default as isDefault,";
    private final String SELECT_SQL_CHARACTER_LENGTH = " character_maximum_length  AS characterLength ";
    private String selectSQL =
            "SELECT " + SELECT_SQL_FIELD + SELECT_SQL_TYPE + SELECT_SQL_MEMO + SELECT_SQL_MUNERIC_LENGTH
                    + SELECT_SQL_NUMERIC_SCALE + SELECT_SQL_ISNULLABLE + SELECT_SQL_EXTRA + SELECT_SQL_ISDEFAULT
                    + SELECT_SQL_CHARACTER_LENGTH + " FROM Information_schema.columns WHERE  table_Name = ?";

    public void excuteSql(Connection conn, String tableName) {
        try {
            PreparedStatement ps = conn.prepareStatement(selectSQL);
            ps.setString(1,  tableName );
            ResultSet rs = ps.executeQuery();
            List<DBMap> tableMeta = Lists.newArrayList();
            while (rs.next()) {
                DBMap dbMap = new DBMap();
                dbMap.setField(rs.getString(1));
                dbMap.setType(rs.getString(2));
                dbMap.setMemo(rs.getString(3));
                dbMap.setMunericLength(rs.getString(4));
                dbMap.setNumericScale(rs.getString(5));
                dbMap.setIsNullable(rs.getString(6));
                dbMap.setExtra(rs.getString(7));
                dbMap.setIsDefault(rs.getString(8));
                dbMap.setCharacterLength(rs.getString(9));
                tableMeta.add(dbMap);
                 System.out.println("Field->" + rs.getString(1));
                 System.out.println("Type->" + rs.getString(2));
                 System.out.println("Memo->" + rs.getString(3));
                 System.out.println("MunericLength->" + rs.getString(4));
                 System.out.println("NumericScale->" + rs.getString(5));
                 System.out.println("IsNullable->" + rs.getString(6));
                 System.out.println("Extra->" + rs.getString(7));
                 System.out.println("IsDefault->" + rs.getString(8));
                 System.out.println("CharacterLength->" + rs.getString(9));
                 System.out.println("####################################");
            }
            tableMap.put(tableName, tableMeta);

            ps = conn.prepareStatement("SELECT table_comment FROM Information_schema.tables WHERE TABLE_SCHEMA='wj_jteach' AND table_Name = ?");
            ps.setString(1, tableName);
            rs = ps.executeQuery();
            while (rs.next()) {
                tableCommentMap.put(tableName, rs.getString(1)+"实体,表名"+tableName);
            }

            this.close(rs, ps, null);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private String DBUrl = "jdbc:mysql://218.0.51.85:3306/wj_jteach?characterEncoding=UTF-8";
    private String DBUser = "root";
    private String DBPwd = "WANGj@center";

    public Connection getConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(DBUrl, DBUser, DBPwd);
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public void close(ResultSet rs, PreparedStatement ps, Connection conn) {
        // 关闭记录集
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        // 关闭声明
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        // 关闭链接对象
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Data
    class DBMap {
        private String field;
        private String type;
        private String memo;
        private String munericLength;
        private String numericScale;
        private String isNullable;
        private String extra;
        private String isDefault;
        private String characterLength;
        // Field->facility_pen
        // Type->tinyint
        // Memo->设施--笔纸 有(1) 无(0)
        // MunericLength->3
        // NumericScale->0
        // IsNullable->NO
        // Extra->0
        // IsDefault->null
        // CharacterLength->null

    }

}
